package com.sinopac.model.RightsManagement;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import netfusion.jbranch.database.DataAccessManager;
import netfusion.jbranch.encrypt.md5.MD5Util;

import org.apache.commons.lang.StringUtils;
import org.apache.struts2.ServletActionContext;

import com.sinopac.entity.RightsManagement.RolesVO;
import com.sinopac.model.core.ModelCore;

public class RightsManagementModel extends ModelCore{
	/**
	 * 確定使用者輸入帳密是否有誤
	 * true: 正確  false: 錯誤
	 * @param username
	 * @param password
	 * @return
	 */
	public Boolean chkUser(String username,String password){
		Boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		String clientIP = ServletActionContext.getRequest().getRemoteAddr();
		System.out.println("ClientIP:"+clientIP);
		try {
			StringBuffer queryString = new StringBuffer("select * from Roles where userid = :username ");
			dam.setObject("username", username);
			dam.setQueryString(queryString.toString());
			List<Map<String,Object>> list = dam.exeQuery();
			if(!list.isEmpty()){
				String userRoles = getRoles(username);
				Map<String,Object> map = list.get(0);
				if(userRoles.equals("9")){
					if(StringUtils.isNotBlank((String)map.get("password"))){
						flag = StringUtils.equals(MD5Util.calc(password), (String)map.get("password"));
						if(!flag){
							// 13 密碼錯誤(登入權限管理平台時驗證)
							insertLoginLog(username, (String)map.get("username"), userRoles, clientIP, "13");
						}else{
							// 01 成功
							insertLoginLog(username, (String)map.get("username"), userRoles, clientIP, "01");
						}
					}else{
						// 13 密碼錯誤(登入權限管理平台時驗證)
						insertLoginLog(username, (String)map.get("username"), userRoles, clientIP, "13");
						flag = true;
					}
				}else{
					// 11 角色權限不正確
					insertLoginLog(username, (String)map.get("username"), userRoles, clientIP, "11");
				}				
			}else{
				// 10 使用者不存在
				insertLoginLog("", "", "", clientIP, "10");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 取得User的角色Roles
	 * @param 
	 * @return
	 */
	public String getRoles(String userId){
		String user_roles = "";
		DataAccessManager dam = new DataAccessManager();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			String sql = "SELECT * FROM Roles WHERE userid = '"+userId+"'";
			dam.setQueryString(sql);
			List<Map<String,Object>> list = dam.exeQuery();
			if(!list.isEmpty()){
				user_roles = list.get(0).get("user_roles").toString();			
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return user_roles;		
	}
	/**
	 * 寫入LoginLog
	 * @return
	 */
	public Boolean insertLoginLog(String userId, String userName, String userRoles, String clientIp, String result){
//		Map<String,String> roleType = new HashMap<String, String>();
//		roleType.put("0", "");
//		roleType.put("1", "行員");
//		roleType.put("2", "主管");
//		roleType.put("3", "查詢");
//		roleType.put("4", "前置");
//		roleType.put("9", "系統管理者");
		Date date = new Date();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String subdate = df.format(date);

		boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		String sql = "INSERT INTO LoginLog(UserID, UserName, RoleType, " +
					 "Source, HostIP, LogDateTime, Result, Memo) "+ 
	                 "VALUES(:UserID, :UserName, :RoleType, :Source, :HostIP, :LogDateTime, :Result, :Memo)";
		dam.setQueryString(sql);
		dam.setObject("UserID", userId);
		dam.setObject("UserName", userName);
		dam.setObject("RoleType", userRoles);
		dam.setObject("Source", "01");
		dam.setObject("HostIP", clientIp);
		dam.setObject("LogDateTime", subdate);
		dam.setObject("Result", result);
		dam.setObject("Memo", "");
		try {
			flag = dam.exeUpdate()>0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * 確定使用者名稱是否有重複
	 * true:已存在 false:可以建置
	 * @param userid
	 * @return boolean
	 */
	public Boolean chkUserName(String userid){
		Boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		try {
			StringBuffer queryString = new StringBuffer("select * from Roles where userid = :userid ");
			dam.setObject("userid", userid);
			dam.setQueryString(queryString.toString());
			List<Map<String,Object>> list = dam.exeQuery();
			flag = !list.isEmpty();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public List<RolesVO> queryUserList(String functionName){
		String sql = "";
		List<RolesVO> result = new ArrayList<RolesVO>();
		RolesVO vo = new RolesVO();
		DataAccessManager dam = new DataAccessManager();
		try {
			if(StringUtils.equals(functionName, "userList")){
				sql = "<>";
			}else if(StringUtils.equals(functionName, "sysList")){
				sql = "=";
			}
			String query = "select * from Roles where user_roles "+sql+" '9' ";
			dam.setQueryString(query);
			List<Map<String,Object>> list = dam.exeQuery();
			for(Map<String,Object> map:list){
				vo = new RolesVO();
				vo.setUserid(map.get("userid")+"");
				vo.setUsername(map.get("username")+"");
				vo.setUser_roles(map.get("user_roles")+"");
				vo.setDeleteFlag(false);
				result.add(vo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public String createUser(String userAccount,String username,String user_roles){
		String message = "";
		DataAccessManager dam = new DataAccessManager();
		dam.setQueryString("insert into Roles(userid,username,user_roles,created_time,updated_time)" +
						  " values(:userid,:username,:user_roles,getdate(),getdate()) ");
		dam.setObject("userid", userAccount);
		dam.setObject("username", username);
		dam.setObject("user_roles", user_roles);
		try {
			int result = dam.exeUpdate();
			if(result>0){
				message = "success";
			}else{
				message = "error";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return message;
	}
	
	public String createAdmin(String userAccount,String username,String password){
		String message = "";
		DataAccessManager dam = new DataAccessManager();
		dam.setQueryString("insert into Roles(userid,username,user_roles,password,created_time,updated_time)" +
						  " values(:userid,:username,'9',:password,getdate(),getdate()) ");
		dam.setObject("userid", userAccount);
		dam.setObject("username", username);
		dam.setObject("password", MD5Util.calc(password));
		try {
			int result = dam.exeUpdate();
			if(result>0){
				message = "success";
			}else{
				message = "error";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return message;
	}
	
	public Boolean deleteUsers(String userid){
		Boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		try {
			dam.setQueryString("delete from Roles where userid = :userid");
			dam.setObject("userid", userid);
			int result = dam.exeUpdate();
			flag = result>0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public Boolean updateUserStatus(String userid,String roles){
		Boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		dam.setQueryString("update Roles set user_roles = :roles,updated_time= getdate() where userid = :userid");
		dam.setObject("roles", roles);
		dam.setObject("userid", userid);
		try {
			int result = dam.exeUpdate();
			flag = result>0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public Boolean updatePassword(String userid,String newPassword){
		Boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		dam.setQueryString("update Roles set password = :password,updated_time= getdate() where userid = :userid");
		dam.setObject("password", MD5Util.calc(newPassword));
		dam.setObject("userid", userid);
		try {
			int result = dam.exeUpdate();
			flag = result>0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
}
